﻿using System;
using System.Linq.Dynamic;
using System.Collections.Generic;
using System.Linq;
using System.Text;
using Buffalo.Lib.Common;
using Ext.Net.MVC;
using Ext.Net;
using System.Web.Mvc;
using Buffalo.SMECommon.DAL.Models;
using System.Data.Linq;
using System.Collections;
using System.Configuration;
using Buffalo.SMECommon.Enum;
using Buffalo.Lib.UI;
using Buffalo.Administration.DAL;
using Buffalo.Administration.DAL.Models;

namespace Buffalo.SMECommon.DAL
{
    public class EmployeeDAL : BaseDAL
    {
        private object Params;
        #region Employee
        public List<Employee> GetListEmployee(int limit, int start, string dir, string sort, string cboOrgUnit, string criteria, string searchText, out int total)
        {
            string whereClause = string.Empty;
            if(!string.IsNullOrEmpty (cboOrgUnit))
                whereClause = string.Format ( "IDOrgUnit = {0}", cboOrgUnit );

            return GetListEntity<Employee>("gc_Employee", whereClause  , limit, start, dir, sort, criteria, searchText, out total);
        }
        private bool CheckEmployeeExist(string code)
        {
            Employee exist = db.Employees.FirstOrDefault(item => item.Code == code);
            return exist != null;
        }

        public Employee GetEmployee(string IDEmployee)
        {
            return GetEntity<Employee>(IDEmployee);
        }
        public bool SaveEmployees(string jsonData)
        {
            try
            {
                return SaveEntities<Employee>(jsonData, new string[] { "Code"});
            }
            catch (Exception e)
            {
                throw e;
            }
        }
        public bool SaveEmployee(Employee employee)
        {
            try
            {
                if (employee == null)
                    throw new Exception("Employee is null");

                if (string.IsNullOrEmpty(employee.Code))
                    throw new Exception("Code is required");

                if (string.IsNullOrEmpty(employee.ID) || employee.ID.Equals("0"))
                {
                    employee.IsActive = (int)enuIsActive.IsActive;
                }
                //save for xxName


                SaveEntity<Employee>(employee, new string[] { "Code"});
                return true;
            }
            catch (Exception e)
            {
                throw e;
            }
        }

        public string ExportEmployeeToExcel(string mainFolder, string excelTemplateFile, string DbConnectionStringEntry, string IDOrgUnit, string criteria, string searchText)
        {
            try
            {
                //do export
                string connectionString = ConfigurationManager.ConnectionStrings[DbConnectionStringEntry].ConnectionString;
                string excelBody = mainFolder + @"\Temp\" + Guid.NewGuid().ToString() + ".xls";
                string excelHeaderBody = mainFolder + @"\Temp\" + Guid.NewGuid().ToString() + ".xls";
                string excelHeaderBodyFooter = mainFolder + @"\Temp\" + Guid.NewGuid().ToString() + ".xls";
                string sqlQueryString = @"SELECT	gc_Employee.Code
                                                    , gc_Employee.FirstName 
                                                    , gc_Employee.LastName
                                                    , gc_Employee.DOB
                                                    , gc_Employee.Sex 
                                                    , gc_Employee.ResidentAddress
                                                    , gc_Employee.PhoneNo
                                                    , gc_Employee.FatherName
                                                    , gc_Employee.MotherName
                                                    , gc_Nation.Code
                                                    , gc_Employee.NationalityName
                                            FROM gc_Employee left join gc_Nation on gc_Employee.IDNation = gc_Nation.ID ";
                string whereClause = " WHERE IsActive= " + (int)enuIsActive.IsActive;
                if (!string.IsNullOrEmpty(IDOrgUnit))
                    whereClause += " and gc_Employee.IDOrgUnit = '" + IDOrgUnit + "'";
                if (!string.IsNullOrEmpty(searchText))
                {
                    switch (criteria)
                    {
                        case "ByCode":
                            whereClause += " and Code like N'%{0}%'";
                            break;
                        case "ByFirstName":
                            whereClause += " and FirstName like N'%{0}%'";
                            break;
                        case "ByLastName":
                            whereClause += " and LastName like N'%{0}%'";
                            break;
                        case "ByDOB":
                            whereClause += " and DOB ={0}";
                            break;
                        default:

                            break;
                    }
                }

                sqlQueryString += whereClause;
                sqlQueryString = string.Format(sqlQueryString, searchText);
                sqlQueryString += " order by gc_Employee.Code ";

                string excelFile = mainFolder + @"\Temp\" + Guid.NewGuid().ToString() + ".xls";
                ExcelHelper excelHelper = new ExcelHelper();
                excelHelper.CellsWrite += excelManager_CellsWriteExportEmployee;
                bool result = excelHelper.ExportToExcel(excelFile, excelTemplateFile, connectionString, sqlQueryString, "B");
                if (result)
                {
                    return excelFile;
                }
                return string.Empty;
            }
            catch (Exception)
            {

                throw;
            }
        }
        private static string FormatDOB(object value)
        {
            if (value == null)
                return string.Empty;
            DateTime dateTimeValue = Util.ToSafeDateTime(value);
            return dateTimeValue.Equals(Util.NullDateTime) ? string.Empty : Util.GetDMYFromDate(dateTimeValue);
        }

        static object excelManager_CellsWriteExportEmployee(string curColName, int curRow, object value)
        {
            //DOB to DD/MM/YYYY
            if (Util.Ascii(curColName) == Util.Ascii("F"))
                return FormatDOB(value);
            if (Util.Ascii(curColName) == Util.Ascii("G"))
                return ExtNetLocalizationHelper.Instance.GetLocalizeString(System.Enum.GetName(typeof(enuSex), Util.ToSafeInt(value)));
            else
                return value;
        }

        public bool ImportEmployeeFromExcel(string excelFile, string connectionString)
        {
            string sheetName = "Sheet1";

            ExcelManager excelManager = new ExcelManager();
            string orgUnitName = Util.ToSafeString(ExcelManager.GetCellValue(excelFile, sheetName, "H6"));
            OrgUnit orgUnit = null;
            if (!string.IsNullOrEmpty(orgUnitName))
                //orgUnit = db.OrgUnits.FirstOrDefault(item => item.Name == orgUnitName);
                orgUnit = new PermissionDAL().GetOrgUnitByName(orgUnitName);

            Params = orgUnit;

            string tableName = "gc_Employee";
            string IdentifierColumn = "ID";
            int startRow = 9;

            List<ExcelTableMapping> listExcelTableMapping = new List<ExcelTableMapping> {
                        new ExcelTableMapping { ExcelColumn = "C", TableColumn = "EmployeeCode", DataType = enuExcelDataType.String }, 
                        new ExcelTableMapping { ExcelColumn = "D", TableColumn = "EmployeeFirstName", DataType = enuExcelDataType.String }, 
                        new ExcelTableMapping { ExcelColumn = "E", TableColumn = "EmployeeLastName", DataType = enuExcelDataType.String }, 
                        new ExcelTableMapping { ExcelColumn = "F", TableColumn = "DOB", DataType = enuExcelDataType.DateTime }, 
                        new ExcelTableMapping { ExcelColumn = "G", TableColumn = "SEX", DataType = enuExcelDataType.String }, 
                        new ExcelTableMapping { ExcelColumn = "H", TableColumn = "ResidentAddress", DataType = enuExcelDataType.String}, 
                        new ExcelTableMapping { ExcelColumn = "I", TableColumn = "PhoneNo", DataType = enuExcelDataType.String }, 
                        new ExcelTableMapping { ExcelColumn = "J", TableColumn = "FatherName", DataType = enuExcelDataType.String},
                        new ExcelTableMapping { ExcelColumn = "K", TableColumn = "MotherName", DataType = enuExcelDataType.String},
                        new ExcelTableMapping { ExcelColumn = "L", TableColumn = "NationName", DataType = enuExcelDataType.String},
                        new ExcelTableMapping { ExcelColumn = "M", TableColumn = "NationalityName", DataType = enuExcelDataType.String},
                        new ExcelTableMapping { ExcelColumn = "N", TableColumn = "IsErased", DataType = enuExcelDataType.String},};

            //import new Employee
            excelManager.CellRead += EmployeeExcelManager_CellRead;
            bool result = excelManager.ImportFromExcel(excelFile, sheetName, connectionString, tableName, listExcelTableMapping, IdentifierColumn, startRow, "B");
            return result;
        }
        void EmployeeExcelManager_CellRead(List<GridCell> listCell)
        {
            try
            {
                OrgUnit orgUnit = (OrgUnit)Params;

                Employee employee = new Employee();
                employee.ID = General.GenerateID();
                employee.IsActive = (int)enuIsActive.IsActive;
                foreach (GridCell cell in listCell)
                {
                    switch (ExcelManager.GetExcelColumnName(cell.Col))
                    {
                        case "C":
                            employee.Code = Util.ToSafeString(cell.Value);
                            break;
                        case "D":
                            employee.FirstName = Util.ToSafeString(cell.Value); ;
                            break;
                        case "E":
                            employee.LastName = Util.ToSafeString(cell.Value);
                            break;
                        case "F":
                            employee.DOB = Util.GetDateFromDMY(Util.ToSafeString(cell.Value));
                            break;
                    }
                }
                //check whether this employee exists
                Employee existed = db.Employees.FirstOrDefault(item => item.Code == employee.Code);
                if (existed != null)
                { 
                    db.Employees.DeleteOnSubmit(existed);
                    employee.ID = existed.ID;
                }
                db.Employees.InsertOnSubmit(employee);
                db.SubmitChanges();
            }
            catch (Exception)
            {

                throw;
            }
        }

        public IEnumerable GetListEmployeeFamily(int limit, int start, string dir, string sort, out int total, string IDEmployee)
        {
            var query = db.EmployeeFamilies
                .Where(item => item.IDEmployee == IDEmployee)
                .OrderBy(sort, dir);

            total = query.Count();

            if (limit > 0)
                query = query.Skip(start).Take(limit);
            return query;
        }

        public bool SaveEmployeeFamilies(string jsonData)
        {
            try
            {
                StoreDataHandler dataHandler = new StoreDataHandler(jsonData);
                ChangeRecords<EmployeeFamily> data = dataHandler.ObjectData<EmployeeFamily>();

                foreach (EmployeeFamily familyRelationship in data.Deleted)
                {
                    db.EmployeeFamilies.Attach(familyRelationship);
                    db.EmployeeFamilies.DeleteOnSubmit(familyRelationship);
                }

                foreach (EmployeeFamily familyRelationship in data.Updated)
                {
                    db.EmployeeFamilies.Attach(familyRelationship);
                    db.Refresh(RefreshMode.KeepCurrentValues, familyRelationship);
                }

                foreach (EmployeeFamily familyRelationship in data.Created)
                {
                    familyRelationship.ID = General.GenerateID();
                    db.EmployeeFamilies.InsertOnSubmit(familyRelationship);
                }


                db.SubmitChanges();
                return true;
            }
            catch (Exception e)
            {
                throw e;
            }
        }
        #endregion

    }
}
